/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.bionic.dao;

import com.bionic.idao.IUserDAO;
import com.bionic.entity.UserType;
import com.bionic.entity.Users;
import com.bionic.jdbc.connection.DAOFactory;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Obuhov Denis
 */
public class UserDAO implements IUserDAO {
    
    Connection connection = null;
    PreparedStatement ptmt = null;
    ResultSet resultSet = null;
    
 
    @Override
    public Users findById(Integer id) {

        final String QUERY = "select u.id, u.first_name, u.last_name, u.login, u.phone, u.email, u.password from mydb.users u where u.id = ?";
        PreparedStatement prst = null;
        ResultSet rs = null;
        Users someUser = null;
        Connection con = null;
        try {
            con = DAOFactory.createConnection();
        } catch (SQLException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        try {
            someUser = new Users(id);
            prst = con.prepareStatement(QUERY);
            prst.setInt(1, id);
            rs = prst.executeQuery();
            if (rs.first()) {

                someUser.setFirstName(rs.getString("first_name"));
                someUser.setLastName(rs.getString("last_name"));
                someUser.setLogin(rs.getString("login"));
                someUser.setPhone(rs.getString("phone"));
                someUser.setEmail(rs.getString("email"));
                someUser.setPassword(rs.getString("password"));



                System.out.println(someUser);
            } else {
                System.out.println("Not data found");
            }


        } catch (SQLException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return someUser;

    }

    @Override
    public ArrayList<Users> findAll() {

        final String QUERY = "select u.id, u.first_name, u.last_name, u.login, u.phone, u.email, u.password from mydb.users u";
        PreparedStatement prst = null;
        ResultSet rs = null;
        ArrayList<Users> userList = null;
        Connection con = null;
        try {
            con = DAOFactory.createConnection();
        } catch (SQLException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        try {
            userList = new ArrayList<Users>();
            prst = con.prepareStatement(QUERY);
            rs = prst.executeQuery();
            Users someUser = null;
            while (rs.next()) {
                someUser = new Users();
                someUser.setId(rs.getInt("id"));
                someUser.setFirstName(rs.getString("first_name"));
                someUser.setLastName(rs.getString("last_name"));
                someUser.setLogin(rs.getString("login"));
                someUser.setPhone(rs.getString("phone"));
                someUser.setEmail(rs.getString("email"));
                someUser.setPassword(rs.getString("password"));

                userList.add(someUser);

                System.out.println(someUser);
            }

        } catch (SQLException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return userList;

    }

    @Override
    public Integer insert(Users someUser) {
        final String QUERY = "insert into mydb.users (id,first_name,last_name,login,phone,email,password,user_type_id)"
                                +"values (mydb.users_seq(),?,?,?,?,?,?,?)";

        PreparedStatement prst = null;
        ResultSet rs = null;
        Connection con = null;
        Integer rowCount = 0;
        
        try {
            con = DAOFactory.createConnection();
        } catch (SQLException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        try {
            prst = con.prepareStatement(QUERY);
            prst.setString(1, someUser.getFirstName());
            prst.setString(2, someUser.getLastName());
            prst.setString(3, someUser.getLogin());
            prst.setString(4, someUser.getPhone());
            prst.setString(5, someUser.getEmail());
            prst.setString(6, someUser.getPassword());
            prst.setInt(7, someUser.getUserTypeId().getId());
            
            rowCount = prst.executeUpdate();

        } catch (SQLException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        System.out.println("Insert rowCount="+rowCount);
        return rowCount;
    }

    @Override
    public Integer update(Users someUser) {
       final String QUERY = "update mydb.users t "
               + "set t.first_name = ? "
               + ",t.last_name =? "
               + ",t.login=? "
               + ",t.phone=? "
               + ",t.email=? "
               + ",t.password=? "
               + ",t.user_type_id=? "
               + "where t.id = ?";
        PreparedStatement prst = null;
        Connection con = null;
        Integer rowCount = 0;
        
        try {
            con = DAOFactory.createConnection();
        } catch (SQLException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        try {
            prst = con.prepareStatement(QUERY);
            prst.setString(1, someUser.getFirstName());
            prst.setString(2, someUser.getLastName());
            prst.setString(3, someUser.getLogin());
            prst.setString(4, someUser.getPhone());
            prst.setString(5, someUser.getEmail());
            prst.setString(6, someUser.getPassword());
            System.out.println("someUser.getUserTypeId().getId()="+someUser.getUserTypeId().getId());
            System.out.println("someUser="+someUser);
            prst.setInt(7, someUser.getUserTypeId().getId());
            prst.setInt(8, someUser.getId());
            
          
            
            rowCount = prst.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        System.out.println("Update rowCount="+rowCount);
        return rowCount;
    }

    @Override
    public Integer deleteById(Integer id) {
        final String QUERY = "delete from mydb.users "
                + " where id = ?";
        PreparedStatement prst = null;
        Connection con = null;
        Integer rowDelete = 0;

        try {
            con = DAOFactory.createConnection();
        } catch (SQLException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        try {
            prst = con.prepareStatement(QUERY);
            prst.setInt(1, id);
            rowDelete = prst.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        System.out.println("Delete user id=" + id + " rowCount=" + rowDelete);
        return rowDelete;
    }

    @Override
    public Users findByUserAndPassword(String login, String pass) {
        final String QUERY = "select u.id, u.first_name, u.last_name, u.login, u.phone, u.email, u.password, u.user_type_id from mydb.users u where u.login = ? and u.password = ?";
        PreparedStatement prst = null;
        ResultSet rs = null;
        Users someUser = null;
        Connection con = null;
        UserType userType = null;
        try {
            con = DAOFactory.createConnection();
        } catch (SQLException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        try {
            prst = con.prepareStatement(QUERY);
            prst.setString(1, login);
            prst.setString(2, pass);
            
            rs = prst.executeQuery();
            if (rs.first()) {
                someUser = new Users();
                someUser.setId(rs.getInt("id"));
                someUser.setFirstName(rs.getString("first_name"));
                someUser.setLastName(rs.getString("last_name"));
                someUser.setLogin(rs.getString("login"));
                someUser.setPhone(rs.getString("phone"));
                someUser.setEmail(rs.getString("email"));
                someUser.setPassword(rs.getString("password"));
                userType = new UserType(rs.getInt("user_type_id"));
                someUser.setUserTypeId(userType);

                
                System.out.println(someUser);
            } else {
                System.out.println("Not data found");
            }


        } catch (SQLException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return someUser;
 }


 
}
